<?php

class StatisticModel extends BpfModel
{

  public function getAllRankList($page = 1, $size = 15)
  {
    $size = max(intval('0' . $size), 1);
    $offset = (max(intval('0' . $page), 1) - 1) * $size;
    $sql = join(' ', [
     'SELECT b.rank, b.nickname, b.headimgurl, b.player_id, FLOOR(b.flowers_history) AS flowers_history, FLOOR(b.flowers) AS flowers from (',
     '  SELECT t.*, @rank := @rank + 1 AS rank',
     '  FROM (SELECT @rank := 0) r, `players` AS t',
     '  ORDER BY t.`flowers_history` DESC',
     ") AS b LIMIT {$offset}, {$size};",
    ]);
    return $this->getModel('mysql')->query($sql)->all();
  }

  public function getAllRank($player_id)
  {
   $sql = join(' ', [
    'SELECT b.rank from (',
    '  SELECT t.*, @rank := @rank + 1 AS rank',
    '  FROM (SELECT @rank := 0) r, `players` AS t',
    '  ORDER BY t.`flowers_history` DESC',
    ') AS b WHERE b.player_id = ?',
   ]);
   return $this->getModel('mysql')->query($sql, [$player_id])->field() ?? 0;
  }

  public function getFriendRankList($player_id, $page = 1, $size = 15)
  {
    $player_id = intval($player_id);
    $size = max(intval('0' . $size), 1);
    $offset = (max(intval('0' . $page), 1) - 1) * $size;
    $sql = join(' ', [
      'SELECT `a`.* FROM (',
      '  SELECT @rank := @rank + 1 AS rank, b.nickname, b.headimgurl, b.player_id, FLOOR(b.flowers_history) AS flowers_history, FLOOR(b.flowers) AS flowers FROM (SELECT @rank := 0) r, ',
      '    ((SELECT `players`.* ',
      '      FROM  `player_friends` inner join `players` on `players`.`player_id` =`player_friends`.`friend_id`',
      "      WHERE `player_friends`.`player_id` = {$player_id}",
      '      ORDER BY `players`.`flowers_history` DESC)) AS `b`',
      ") `a` LIMIT {$offset }, {$size}",
    ]);
    return $this->getModel('mysql')->query($sql)->all();
  }

  public function getFriendRank($player_id)
  {
    $player_id = intval($player_id);
    $sql = join(' ', [
      'SELECT a.rank FROM (',
      '  SELECT @rank := @rank + 1 AS rank, b.nickname, b.headimgurl, b.player_id, FLOOR(b.flowers_history) AS flowers_history, FLOOR(b.flowers) AS flowers FROM (SELECT @rank := 0) r, ',
      '    ((SELECT `players`.* ',
      '      FROM  `player_friends` inner join `players` on `players`.`player_id` =`player_friends`.`friend_id`',
      "      WHERE `player_friends`.`player_id` = {$player_id}",
      '      ORDER BY `players`.`flowers_history` DESC)) AS `b`',
      ') `a` WHERE a.player_id = ?',

    ]);
    return $this->getModel('mysql')->query($sql, [$player_id])->field() ?? 0;
  }

  public function last7Flower($player_id)
  {
    return $this->getModel('mysql')->query('SELECT `date`, FLOOR(`flowers`) AS `flowers` FROM `players_flowers_statistics` WHERE `date` >= ? AND `player_id` = ? ORDER BY `date`', [
      date('Ymd', mktime(0, 0, 0, date('m'), date('d') - 7 , date('Y'))), $player_id
    ])->all();
  }


}
